﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Xml;
namespace WPFExcelReport
{
    class DSTAB50Report : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string WBBTP;
        string DocType;
        string CellName = "";
        string CellValue = "";
        string strCompany;
        string strExcelFile;
        string Today
        {
            get
            {
                DateTime now = DateTime.Now;
                return now.Year.ToString() + Right("00" + now.Month.ToString(), 2) + Right("00" + now.Day.ToString(), 2);
            }
        }
        #endregion
        public DSTAB50Report()
            : base()
        {
        }
        public DSTAB50Report(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            DocType = dicParamater["DocType"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
        }
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {

                strExcelFile = @"DST\DSTStandard.xls";
                DataRow row = dsSource.Tables["ABPOS"].Rows[0];
                string KRS = row["ABPAUFKRS"].ToString().Trim();
                string LFD = row["ABPAUFLFD"].ToString().Trim();
                string POS = row["ABPAUFPOS"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string newfilename = KRS + "-" + flfd;
                exAgent.CheckWorkBook(newfilename);
                LocalFile(newfilename);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInEXCEL()
        {
            try
            {
                string value = "";
                DataRow row = null;
                int x = 1, y = 1;
                x = 3; y = 5;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);
                value = row["ABKKDAUFTNR"].ToString();
                x = 3; y = 14;
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 5;
                        exAgent.SetCellValue(x, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 5; y = 5;
                        exAgent.SetCellValue(x, y, value);
                    }
                }
                int rownum = -1;

                foreach (DataRow dwABPOS in dsSource.Tables["ABPOS"].Rows)
                {
                    rownum++;
                    string art = dwABPOS["ABPART"].ToString();
                    string var = dwABPOS["ABPVAR"].ToString();
                    string color = dwABPOS["ABPFARBE"].ToString();
                    value = dwABPOS["ABPLIEFDAT"].ToString();

                    x = 4; y = 30;
                    exAgent.SetCellValue(x, y, value);
                    value = dwABPOS["ABPFILLER1"].ToString();
                    x = 8; y = 5;
                    exAgent.SetCellValue(x, y, value);
                     
                    #region=====Technical============
                    var query = from rows in dsSource.Tables["GSDAT"].AsEnumerable()
                                where rows["GSART"].ToString() == art
                                & rows["GSVAR"].ToString() == var
                                select rows;
                    foreach (DataRow rw in query)
                    {
                        if (var.Trim() != "")
                            value = art.Trim() + "/" + var;
                        exAgent.SetCellValue(12 + rownum * 2, 3, value);
                        value = rw["GSGARNBEZ"].ToString();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(12 + rownum * 2 + 1, 3, value.Trim());
                    }
                    #endregion
                    #region======color=======
                    query = from rows in dsSource.Tables["GSCOLOR"].AsEnumerable()
                            where rows["GSCART"].ToString() == art
                            & rows["GSCVAR"].ToString() == var
                            & rows["GSCFARBE"].ToString() == color
                            select rows;
                    foreach (DataRow rw in query)
                    {
                        exAgent.SetCellValue(12 + rownum * 2, 19, color.Trim());
                         value = rw["GSCBEZ"].ToString().Trim();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(12 + rownum * 2 + 1, 19, value);
                        value = rw["GSCMUFRB"].ToString();
                        value = GetGB2312Value(value).Trim();
                        exAgent.SetCellValue(12 + rownum * 2, 24, value);
                    }
                    #endregion

                    #region=====composition=======
                    query = from rows in dsSource.Tables["GSMATK"].AsEnumerable()
                            where rows["GSMART"].ToString() == art
                            & rows["GSMVAR"].ToString() == var
                            select rows;
                    string composition = "";
                    foreach (DataRow rw in query)
                    {       
                        string sg = rw["GSMRBEZ"].ToString().Trim();
                        string bez = rw["GSMRSPZ"].ToString();
                        bez = GetGB2312Value(bez).Trim();
                        double ratio = System.Convert.ToDouble( rw["GSMRANT"].ToString()) * 0.1;
                        composition += ratio + "%" + sg + bez;
                    }
                    exAgent.SetCellValue(12 + rownum * 2, 9, composition);
                    #endregion

                    #region====price=======
                    /*value = dwABPOS["ABPSOLLMENGE6"].ToString();
                    value = System.Math.Round(System.Convert.ToDouble(value) * 0.01, 3).ToString() + "kg";
                    exAgent.SetCellValue(4, 27, value);*/
                    query = from rows in dsSource.Tables["ABPR"].AsEnumerable()
                            where rows["ARAUFKRS"].ToString() == dwABPOS["ABPAUFKRS"].ToString()
                            & rows["ARAUFLFD"].ToString() == dwABPOS["ABPAUFLFD"].ToString()
                            & rows["ARAUFPOS"].ToString() == dwABPOS["ABPAUFPOS"].ToString()
                            select rows;
                    double wert = 0;
                    foreach (DataRow rw in query)
                    {
                        value = rw["ARWERT"].ToString();
                        wert += System.Math.Round(System.Convert.ToDouble(value) * 0.001, 2);
                    }
                    //exAgent.SetCellValue(12 + rownum * 2, 24,  wert + "元/kg");
                    #endregion
                    #region====yarn=========
                    double total = 0;
                    query = from rw in dsSource.Tables["GAAUF"].AsEnumerable()
                            where rw["GAAUFKRS"].ToString() == dwABPOS["ABPAUFKRS"].ToString()
                            & rw["GAAUFLFD"].ToString() == dwABPOS["ABPAUFLFD"].ToString()
                            & rw["GAAUFPOS"].ToString() == dwABPOS["ABPAUFPOS"].ToString()
                            & rw["GASA"].ToString() == "310"
                            select rw;
                    int count = 1;
                    count = query.Count();
                    foreach (DataRow rw in query)
                    {
                        double gakg1 = System.Convert.ToDouble(rw["GAKG1"].ToString()) * 0.01;
                        string gaart = rw["GAART"].ToString();
                        string gavar = rw["GAVAR"].ToString();
                        string gacolor = rw["GAFARBE"].ToString();
                        string yarn = gaart.Trim();
                        if (gavar.Trim() != "")
                            yarn += "/" + gavar.Trim();
                        if (gacolor.Trim() != "")
                        {
                            if (gacolor != "000000")
                            {
                                yarn += "/" + gacolor.Trim();
                            }
                        }
                        if (count > 1)
                        {
                            yarn += ":" + gakg1 ;
                        }
                        else
                        {
                            yarn = gakg1.ToString() ;
                        }
                        double ABPEAKG = System.Convert.ToDouble(dwABPOS["ABPEAKG"].ToString()) * 0.01;
                        double ABPEASTK = System.Convert.ToDouble(dwABPOS["ABPEASTK"].ToString()) * 0.01;
                        exAgent.SetCellValue(12 + rownum * 2, 24, yarn);
                        exAgent.SetCellValue(12 + rownum * 2, 27, ABPEAKG);
                        exAgent.SetCellValue(12 + rownum * 2, 30, ABPEASTK);
                        //ABPEAKG ABPEASTK
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillinVK35()
        {
            try
            {
                string value = "";
                DataRow row = null;
                int x = 1, y = 1;
                x = 3; y = 5;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);
                value = row["ABKKDAUFTNR"].ToString();
                x = 3; y = 14;
                exAgent.SetCellValue(x, y, value);
                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 5;
                        exAgent.SetCellValue(x, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 5; y = 5;
                        exAgent.SetCellValue(x, y, value);
                    }
                }
                int rownum = 11;
                //获取WASTGA
                //获取BASTGA
                //获取坯布长度
                //获取经轴长度
                //按品种排序，按纱线，色号排序
                //找到纱线列表，依次找到经纱数量，纬纱数量
                Dictionary<string, int> YarnList = new Dictionary<string, int>();
                string processed = "";
                var query =( from DataRow rw in dsSource.Tables["WAFARBEN"].AsEnumerable()
                            where rw["WAFZUART"].ToString() == "C"
                             select rw).OrderBy(a => a["WAFART"]).ThenBy(a => a["WAFZUSATZ"]);
                //SELECT WAFART,WAFVAR, WAFZUSATZ,WAFMUDESS,WAFGRPBEZ FROM WAFARBEN WHERE WAFART LIKE 'D3150A%' AND WAFZUART='C'
                foreach (DataRow rwf in query)
                {
                    rownum++;
                    string art = rwf["WAFART"].ToString();
                    string var = rwf["WAFVAR"].ToString();
                    string color = rwf["WAFZUSATZ"].ToString();
                    if (rwf["WAFMUDESS"].ToString().Trim() == "") continue;
                    double gleng = System.Convert.ToDouble(rwf["WAFMUDESS"]);
                    double bleng = System.Convert.ToDouble(rwf["WAFGRPBEZ"]);
                    YarnList = new Dictionary<string, int>();
                    x=rownum;
                    y = 1;
                    exAgent.SetCellValue(x,y,art.Trim()+"."+color.Trim(),true);
                    exAgent.SetCellValue(x, 21, gleng,true);
                    exAgent.SetCellValue(x, 25, bleng,true);
                   #region=====WASTGA============
                    var qweft = from DataRow rows in dsSource.Tables["WASTGA"].AsEnumerable()
                                where rows["WAGART"].ToString() == art
                                & rows["WAGVAR"].ToString() == var
                                & rows["WAGWCOL"].ToString() == color
                                orderby rows["WAGGFARBE"].ToString()
                                select rows;
                    foreach (DataRow rw in qweft)
                    {
                        //WAGGART,WAGGVAR,WAGGFARBE
                        string yarn="";
                        string WAGGART = rw["WAGGART"].ToString();
                        string WAGGVAR = rw["WAGGVAR"].ToString();
                        string WAGGFARBE = rw["WAGGFARBE"].ToString();
                        yarn=WAGGART;
                        if (WAGGVAR.Trim() != "")
                            yarn+= "/" + WAGGVAR;
                        if (WAGGFARBE.Trim() != "" || WAGGFARBE.Trim() != "000000")
                        {
                            yarn += "/" + WAGGFARBE;
                        }
                        x = rownum;
                        y = 6;
                        YarnList.Add(yarn, x);
                        yarn = WAGGART;
                        if (WAGGVAR.Trim() != "")
                            yarn += "/" + WAGGVAR;
                        exAgent.SetCellValue(x, y, yarn);
                        y = 11;
                        exAgent.SetCellValue(x, y,  WAGGFARBE);
                        #region=====COLOR============
                        var queryc = from rows in dsSource.Tables["GSCOLOR"].AsEnumerable()
                                     where rows["GSCART"].ToString() == WAGGART
                                     & rows["GSCVAR"].ToString() == WAGGVAR
                                     & rows["GSCFARBE"].ToString() == WAGGFARBE
                                     select rows;
                        foreach (DataRow qc in queryc)
                        {
                            string cdes = qc["GSCBEZ"].ToString();
                            cdes = GetGB2312Value(cdes);
                            exAgent.SetCellValue(x, y, WAGGFARBE.Trim()+"/"+cdes);
                        }
                        #endregion
                        double WAGKGBRUT = System.Convert.ToDouble(rw["WAGKGBRUT"].ToString()) * 0.01;
                        double kg =System.Math.Round( WAGKGBRUT * gleng*0.01*0.1,2);
                        x = rownum;
                        y = 18;
                        exAgent.SetCellValue(x, y, kg);                        
                        rownum++;
                    }
                    #endregion
                    #region=====BASTGA============
                    var qwarp = from DataRow rows in dsSource.Tables["BASTGA"].AsEnumerable()
                                where rows["BAGART"].ToString() == art
                                & rows["BAGFARBE"].ToString() == color
                                orderby rows["BAGGFARBE"].ToString()
                                select rows;
                    foreach (DataRow rw in qwarp)
                    {
                        //WAGGART,WAGGVAR,WAGGFARBE
                        string yarn = "";
                        string BAGGART = rw["BAGGART"].ToString();
                        string BAGGVAR = rw["BAGGVAR"].ToString();
                        string BAGGFARBE = rw["BAGGFARBE"].ToString();
                        yarn = BAGGART;
                        if (BAGGVAR.Trim() != "")
                            yarn += "/" + BAGGVAR;
                        if (BAGGFARBE.Trim() != "" || BAGGFARBE.Trim() != "000000")
                        {
                            yarn += "/" + BAGGFARBE;
                        }
                        x = rownum;
                        y = 1;
                        var qyarn = from qy in YarnList.AsQueryable()
                                    where qy.Key == yarn
                                    select qy;
                        foreach (KeyValuePair<string, int> kp in qyarn)
                        {
                            x = kp.Value;
                        }
                        if (qyarn.Count() == 0)
                        {
                            rownum++;
                            x = rownum;
                            YarnList.Add(yarn, x);                            
                        }
                        yarn = BAGGART;
                        if (BAGGVAR.Trim() != "")
                            yarn += "/" + BAGGVAR;
                        y = 6;
                        exAgent.SetCellValue(x, y, yarn);
                        y = 11;
                        exAgent.SetCellValue(x, y, BAGGFARBE);
                        #region=====COLOR============
                        var queryc = from rows in dsSource.Tables["GSCOLOR"].AsEnumerable()
                                     where rows["GSCART"].ToString() == BAGGART
                                     & rows["GSCVAR"].ToString() == BAGGVAR
                                     & rows["GSCFARBE"].ToString() == BAGGFARBE
                                     select rows;
                        foreach (DataRow qc in queryc)
                        {
                            string cdes = qc["GSCBEZ"].ToString();
                            cdes = GetGB2312Value(cdes);
                            exAgent.SetCellValue(x, y, BAGGFARBE.Trim() + "/" + cdes);
                        }
                        #endregion
                        double BAGKGBRUT = System.Convert.ToDouble(rw["BAGKGBRUT"].ToString()) * 0.01;
                        double kg = System.Math.Round(BAGKGBRUT * bleng * 0.01 * 0.01, 2);
                        y = 15;
                        exAgent.SetCellValue(x, y, kg);                        
                    }
                    #endregion                  
                    foreach (KeyValuePair<string, int> kp in YarnList)
                    {
                        string yarn = kp.Key;
                        x = kp.Value;
                        double ABPEAKG = 0, ABPEASTK = 0;
                        var qab = from DataRow ab in dsSource.Tables["ABPOS"].AsEnumerable()
                                  where ab["ABPART"].ToString() + ab["ABPVAR"].ToString() + ab["ABPFARBE"].ToString()
                                  == yarn
                                  select ab;
                        foreach (DataRow ab in qab)
                        {
                            ABPEAKG += System.Convert.ToDouble(ab["ABPEAKG"].ToString()) * 0.01;
                            ABPEASTK += System.Convert.ToDouble(ab["ABPEASTK"].ToString()) * 0.01;
                        }
                        //exAgent.SetCellValue(x, 21, ABPEAKG);
                        //exAgent.SetCellValue(x, 21, ABPEASTK);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInGB12()
        {
            try
            {
                string value = "";
                DataRow row = null;
                int x = 1, y = 1;
                x = 3; y = 5;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);
                exAgent.SetCellValue(x + 42, y, value);
                exAgent.SetCellValue(x + 84, y, value);
                value = row["ABKKDAUFTNR"].ToString();
                x = 3; y = 14;
                exAgent.SetCellValue(x, y, value);
                exAgent.SetCellValue(x + 42, y, value);
                exAgent.SetCellValue(x + 84, y, value);
                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today);
                exAgent.SetCellValue(x + 42, y, Today);
                exAgent.SetCellValue(x + 84, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 5;
                        exAgent.SetCellValue(x, y, value);
                        exAgent.SetCellValue(x + 42, y, value);
                        exAgent.SetCellValue(x + 84, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 5; y = 5;
                        exAgent.SetCellValue(x, y, value);
                        exAgent.SetCellValue(x + 42, y, value);
                        exAgent.SetCellValue(x + 84, y, value);
                    }
                }
                int rownum = -1;
                string remark = "";
                value = "";
                double menge5 = 0.0;
                Double menge = 0.0 ;
                foreach (DataRow dwABPOS in dsSource.Tables["ABPOS"].Rows)
                {
                    value = dwABPOS["ABPFILLER1"].ToString().Trim();
                    x = 8; y = 5;
                    value = GetGB2312Value(value);
                    exAgent.SetCellValue(x, y, value);
                    exAgent.SetCellValue(x + 42, y, value);
                    exAgent.SetCellValue(x + 84, y, value);
                    value = dwABPOS["ABPNEUBEN"].ToString().Trim();
                    exAgent.SetCellValue(3, 24, value);
                    exAgent.SetCellValue(45, 24, value);
                    exAgent.SetCellValue(87, 24, value);
                }
                x = 0;
                foreach (DataRow dw in dsSource.Tables["VKKOPF"].Rows)
                {
                    value = dw["VKKORTU"].ToString().Trim();
                    exAgent.SetCellValue(12 + x, 3, value);
                    exAgent.SetCellValue(12 + x + 42, 3, value);
                    exAgent.SetCellValue(12 + x + 84, 3, value);
                    value = dw["VKKKOMENGE"].ToString().Trim();
                    exAgent.SetCellValue(12 + x, 21, value);
                    exAgent.SetCellValue(12 + x + 42, 21, value);
                    exAgent.SetCellValue(12 + x + 84, 21, value);
                    value = dw["VKKZUSATZ"].ToString().Trim();
                    exAgent.SetCellValue(12 + x, 27, value);
                    exAgent.SetCellValue(12 + x + 42, 27, value);
                    exAgent.SetCellValue(12 + x + 84, 27, value);
                    x = x + 1;
                }
                
                /*
                var query = from DataRow ga in dsSource.Tables["GAAUF"].AsEnumerable()
                            where ga["GASA"].ToString() == "713"
                            select ga;
                string Var;
                string Yarn = "";
                string art = "", var = "", color = "";
                Dictionary<string, int> YarnList = new Dictionary<string, int>();
                Dictionary<string, double> YarnQTY = new Dictionary<string, double>();
                double kg = 0;
                double qty = 0;
                foreach (DataRow ga in query)
                {
                    Yarn = "";
                    art = ga["GAART"].ToString();
                    Var = ga["GAVAR"].ToString();
                    color = ga["GAFARBE"].ToString();
                    kg = System.Convert.ToDouble(ga["GAKG1"].ToString()) * 0.01;
                    Yarn = art;
                    if (Var.Trim() != "")
                    {
                        Yarn +="/" + Var;
                    }
                    if (color.Trim() != "" && color.Trim() != "000000")
                    {
                        Yarn += "/"+color;
                    }
                    var qyarn = from qy in YarnList.AsQueryable()
                                where qy.Key == Yarn
                                select qy;
                    foreach (KeyValuePair<string, int> kp in qyarn)
                    {
                        x = kp.Value;
                    }
                    if(qyarn.Count()==0)
                    {
                        rownum++;
                        x = rownum;
                        YarnList.Add(Yarn, x);
                    }
                    qty=0;
                    var qtyarn = from qy in YarnQTY.AsQueryable()
                                where qy.Key == Yarn
                                select qy;
                    foreach (KeyValuePair<string, double> kpx in qtyarn)
                    {
                        qty = kpx.Value;
                        qty+=kg; 
                    }
                    if (qtyarn.Count() == 0)
                    {
                        qty = kg;
                        YarnQTY.Add(Yarn, qty);
                    }
                    else
                    {
                        YarnQTY[Yarn] = qty;
                    }*/
                    /*exAgent.SetCellValue(12 + x*2, 3, Yarn, true);
                     exAgent.SetCellValue(12 + x*2 , 21, qty, false);*/
                    /*exAgent.SetCellValue(12 + x, 3, Yarn, true);
                    exAgent.SetCellValue(12 + x + 42, 3, Yarn, true);
                    exAgent.SetCellValue(12 + x + 84, 3, Yarn, true);
                    exAgent.SetCellValue(12 + x, 21, qty, false);
                    exAgent.SetCellValue(12 + x + 42, 21, qty, false);
                    exAgent.SetCellValue(12 + x + 84, 21, qty, false);
                    #region=====COLOR============
                    var queryc = from rows in dsSource.Tables["GSCOLOR"].AsEnumerable()
                                 where rows["GSCART"].ToString() == art
                                 & rows["GSCVAR"].ToString() == var
                                 & rows["GSCFARBE"].ToString() == color
                                 select rows;
                    foreach (DataRow rw in queryc)
                    {
                        value = rw["GSCBEZ"].ToString();
                        value = GetGB2312Value(value);
                      */  /*exAgent.SetCellValue(12 + x*2 + 1, 16, color.Trim());
                       exAgent.SetCellValue(12 + x*2 + 2, 16, value.Trim());*/
                   /*     exAgent.SetCellValue(12 + x + 1, 16, color.Trim());
                        exAgent.SetCellValue(12 + x  + 42 + 1, 16, color.Trim());
                        exAgent.SetCellValue(12 + x  + 84 + 1, 16, color.Trim());
                        exAgent.SetCellValue(12 + x  + 2, 16, value.Trim());
                        exAgent.SetCellValue(12 + x  + 42 + 2, 16, value.Trim());
                        exAgent.SetCellValue(12 + x  + 84 + 2, 16, value.Trim());
                    }
                    #endregion 
                    #region=====Technical============
                    var query1 = from rows in dsSource.Tables["GSDAT"].AsEnumerable()
                                where rows["GSART"].ToString() == art
                                & rows["GSVAR"].ToString() == var
                                select rows;
                    foreach (DataRow rw in query1)
                    {
                        *//*exAgent.SetCellValue(12 + x * 2, 3, Yarn);
                        value = rw["GSGARNBEZ"].ToString();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(12 + x * 2 + 1, 3, value.Trim());*/
                        /*exAgent.SetCellValue(12 + x , 3, Yarn);
                        exAgent.SetCellValue(12 + x  + 42, 3, Yarn);
                        exAgent.SetCellValue(12 + x  + 84, 3, Yarn);
                        value = rw["GSGARNBEZ"].ToString();
                        value = GetGB2312Value(value);
                        exAgent.SetCellValue(12 + x  + 1, 3, value.Trim());
                        exAgent.SetCellValue(12 + x  + 42 + 1, 3, value.Trim());
                        exAgent.SetCellValue(12 + x  + 84 + 1, 3, value.Trim());
                    }
                    #endregion 

                    #region=====composition=======
                   var query2 = from rows in dsSource.Tables["GSMATK"].AsEnumerable()
                            where rows["GSMART"].ToString() == art
                            & rows["GSMVAR"].ToString() == var
                            select rows;
                    string composition = "";
                    foreach (DataRow rw in query2)
                    {
                        string sg = rw["GSMRBEZ"].ToString().Trim();
                        string bez = rw["GSMRSPZ"].ToString();
                        bez = GetGB2312Value(bez).Trim();
                        double ratio = System.Convert.ToDouble(rw["GSMRANT"].ToString()) * 0.1;
                        //composition += ratio + "%" + sg + bez;
                        composition += ratio + "%" + sg;
                    }
                   */ /*exAgent.SetCellValue(12 + x * 2, 11, composition);*/
                    /*exAgent.SetCellValue(12 + x , 11, composition);
                    exAgent.SetCellValue(12 + x  + 42, 11, composition);
                    exAgent.SetCellValue(12 + x  + 84, 11, composition);
                    #endregion 
        
                }*/
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void HideNullRows(int pages)
        {
            try
            {
                exAgent.HideRows((pages + 1) * 25 + 1, 1, 500, 999);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root;
                string dr0 = "";

                #region========================LOCAL=====================
                drlocal = Path.Combine(PathHelper.ApplicationPath, @"Report");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = root;
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "AB50DOC";
            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\DST\DSTStandard.xls");
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent = new ExcelX.ExcelLib.ExcelAgent(dicParamater);
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                string newfilename = orderno;
                Standard = "AB50GB12";
                List<string> Standards = new List<string>();
                Standards.Add("AB50GB12");
                Standards.Add("AB50VK35");
                Standards.Add("AB50DOC");
                exAgent.CreateNewSheets(newfilename, Standards, pwd);
                exAgent.SetCurrentSheet("AB50GB12");
                exAgent.RenameSheet("原纱领用");
                FillInGB12();
                exAgent.Save();
                exAgent.SetCurrentSheet("AB50VK35");
                exAgent.RenameSheet("花号用纱");
                FillinVK35();
                exAgent.Save();
                exAgent.SetCurrentSheet("AB50DOC");
                exAgent.RenameSheet("纱线生产单");
                FillInEXCEL();
                filename = exAgent.FileName; 
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
                exAgent.SetCurrentSheet("原纱领用");
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.Save();
                exAgent.Close();
            }
            return filename;
        }

        private void LocalFile(string ordernumer)
        {
            string fileabsname = ordernumer + ".xls";
            string localFile = @"C:\Windows\Temp\" + fileabsname;
            string tempfile = "";
            try
            {
                tempfile = CreateFile(fileabsname);
                localFile = @"C:\IntexReport\Production\" + fileabsname;
                if (System.IO.File.Exists(localFile))
                {
                    System.IO.File.Delete(localFile);
                }
                File.Move(tempfile, localFile);
                System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}